import logging

import pandas as pd
from flask import request, redirect, url_for, flash
from werkzeug.utils import secure_filename
from src.db_models.base import db
from src.db_models.models import Term, TermCollection
from flask_jwt_extended import jwt_required, get_jwt_identity
import os

# 设置日志
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

# 定义允许上传的文件类型
ALLOWED_EXTENSIONS = {'csv', 'xlsx'}


# 检查文件扩展名是否有效
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


# 解析 CSV 文件并保存到数据库
def load_csv_to_db(csv_file, term_collection_id):
    df = pd.read_csv(csv_file)

    required_columns = ['CN', 'EN', 'example']
    for column in required_columns:
        if column not in df.columns:
            raise ValueError(f"列 {column} 在 CSV 中不存在")

    for index, row in df.iterrows():
        term = Term(
            CN=str(row['CN']),
            EN=str(row['EN']),
            example=str(row.get('example', '')),  # 如果没有例句，则为空字符串
            term_collection_id=term_collection_id
        )
        db.session.add(term)

    db.session.commit()


def load_excel_to_db(excel_file, term_collection_id):
    try:
        # 读取 Excel 文件到 DataFrame
        df = pd.read_excel(excel_file)

        # 检查列是否存在
        required_columns = ['CN', 'EN']
        for column in required_columns:
            if column not in df.columns:
                raise ValueError(f"列 {column} 在 Excel 中不存在")

        # 遍历 DataFrame 每一行并插入数据库
        for index, row in df.iterrows():
            term = Term(
                CN=str(row['CN']),  # 转为字符串，确保不会有类型冲突
                EN=str(row['EN']),
                example=str(row.get('example', '')),  # 如果没有例句，则为空字符串
                term_collection_id=term_collection_id,
            )
            db.session.add(term)

        # 提交会话到数据库
        db.session.commit()
        logger.info(f"成功将 {len(df)} 条记录插入数据库！")

    except Exception as e:
        db.session.rollback()
        logger.error(f"在处理 Excel 文件时发生错误: {str(e)}")
        raise


# 处理上传的文件
def handle_file_upload(file, table_name, category, user_id):
    try:
        # 创建新的术语集
        term_collection = TermCollection(
            user_id=user_id,
            origin_user_id=user_id,
            table_name=table_name,
            category=category
        )
        db.session.add(term_collection)
        db.session.commit()  # 先保存术语集

        # 检查文件格式并处理
        filename = secure_filename(file.filename)
        file_extension = filename.rsplit('.', 1)[1].lower()

        if file_extension == 'csv':
            load_csv_to_db(file, term_collection.id)
        elif file_extension == 'xlsx':
            load_excel_to_db(file, term_collection.id)
        else:
            raise ValueError("不支持的文件格式")

        return True
    except Exception as e:
        db.session.rollback()  # 如果出错，回滚数据库事务
        raise e  # 重新抛出异常，让上层函数处理
